Configuring: Replace Partial Strings
Select one field at a time in order to find and replace or remove a string. The resulting modified dataset will contain the replaced string or the entire string row will be removed from the dataset.
Worked example: Replace partial strings
Steps to configure
-
String replacement: Find and replace or remove the string in the selected field.
-
Execution: A modified dataset is created, containing either the replaced string or having the string rows removed.
Step 1: String replacement
Select the field in which you want to find a certain string value, and enter the value to be replaced or removed.
-
The case sensitivity of this string value is determined by your installation configuration of SQL Server. Default installation of SQL Server is case insensitive.
-
To replace a string value, enter the replacement text.
-
The replacing value must be a valid value.
-
Alternatively replace the value with Null.
-
Default: The first string field is automatically selected.
-
Minimum configuration: The user can only select one field to modify at a time, and find and replace one string at a time.
Examples of find variables:
Variable |
What it means |
Example |
Explanation |
% |
Zero or more characters |
%ing% will search for any string containing "ing". |
There can be zero or more characters before and after "ing". |
_ |
Exactly one character |
A_ will search for any string with an "A" followed by one character. |
The operation will search for a string starting with A and any one character following it. |
0 - 9 or A - Z |
Any single number or alphabetic character included |
%3D% will search for any string containing 3D. |
All the strings containing "3D" will be replaced with your specified value. |
^0 - ^9 or ^A - ^Z |
Any single number or alphabetic character not included |
^7^FA will search for a string not containing the numbers 7 and F before an included A. |
The operation will search for a string where the first character is NOT a "7", the second character is NOT an "F" and the third character IS an "A" |
[_] |
The character _ |
%A[_]_ will search for the string A_ followed by any one character |
[_] refers to the actual character "_" and NOT the variable that searches for any one character. |
[%] |
The character % |
^6[%] will search for the character % that is not preceded by 6 |
[%] refers to the actual character "%" and NOT the variable that searches for zero or more characters. |
Step 2: Execute
A modified dataset will be created with the string replaced with the user’s value or the row that contained the string will be deleted. Note that as you remove strings from the different data fields, all the row containing these string values will be removed from the total dataset.
Related topics: